Skip to main content

UNION

Overview

Use UNION keyword to combine several queries together. As a result, you get a dataset with fields returned by all included in the UNION queries chronologically arranged by their timestamps. UNION can be applied to querying from the same or different streams.

Polymorphic Dataset

You get a polymorphic dataset as a result of a UNION in case it combines messages of different classes.

Examples with UNION keyword
-- UNION of these two queries will return a polymorphic dataset with fields of both classes TradeMessage and BestBidOfferMessage
SELECT
trade.price AS "price",
trade.size AS "size"
TYPE "TradeMessage"
FROM kraken
ARRAY JOIN entries[THIS IS KrakenTradeEntry] AS trade

UNION

SELECT
bbo[side == ASK].price AS "offerPrice",
bbo[side == ASK].size AS "offerSize",
bbo[side == BID].price AS "bidPrice",
bbo[side == BID].size AS "bidSize"
TYPE "BestBidOfferMessage"
FROM kraken
ARRAY JOIN (entries AS array(L1entry))[THIS IS NOT NULL] AS bbo
-- UNION of these two queries will return a polymorphic dataset with fields of both classes Bar1min and Bar5min
WITH
entries[THIS IS TradeEntry] AS 'entries'
SELECT
sum{}(sum(entries.size)) AS 'volume',
first{}(entries[0].price) AS 'open',
last{}(entries[-1].price) AS 'close',
max{}(max(entries.price)) AS 'high',
min{}(min(entries.price)) AS 'low'
TYPE "Bar1min"
FROM binance
OVER TIME(1m)
WHERE symbol == 'BTC/USDT' AND notEmpty(entries)

UNION

WITH
entries[THIS IS TradeEntry] AS 'entries'
SELECT
sum{}(sum(entries.size)) AS 'volume',
first{}(entries[0].price) AS 'open',
last{}(entries[-1].price) AS 'close',
max{}(max(entries.price)) AS 'high',
min{}(min(entries.price)) AS 'low'
TYPE "Bar5min"
FROM binance
OVER TIME(5m)
WHERE symbol == 'BTC/USDT' AND notEmpty(entries)
tip

You can also use RECORD ... TYPE ... WHEN construction to produce a polymorphic data set in a single query.

Fixed-Type Dataset

In case UNION combines messages of the same class, you get a fixed type dataset with fields from all the involved queries merged in one class. In case of identical column names, UNION places results in one column chronologically.

tip

It is not possible to UNION two fields with the same Name but different data types. For example, field Price of integer and string data types cannot be combined by UNION in one field.

-- UNION of these two queries will return a fixed type dataset with fields from both queries' results combined in one TradeAndBBO class.
SELECT
trade.price AS "price",
trade.size AS "size"
TYPE "TradeAndBBO"
FROM kraken
ARRAY JOIN entries[THIS IS KrakenTradeEntry] AS trade

UNION

SELECT
bbo[side == ASK].price AS "offerPrice",
bbo[side == ASK].size AS "offerSize",
bbo[side == BID].price AS "bidPrice",
bbo[side == BID].size AS "bidSize"
TYPE "TradeAndBBO"
FROM kraken
ARRAY JOIN (entries AS array(L1entry))[THIS IS NOT NULL] AS bbo

Casting to a Common Type

You can take messages of two different types, lets say type "TradeMessage" and type "BestBidOfferMessage" and UNION them under one common message type for example type "NewType". As the result of such a query, you will get a fixed type dataset with fields from both classes combined in one new class NewType.

Stream UNION

caution

This feature is available starting from QQL 5.6.

If you want to perform the same action on different streams, you can use UNION in a single query. This way, you can have one message source subscribed to several streams.

REVERSE and LIVE queries are also supported with UNIONS. Refer to Queries to learn more.

Restrictions

  • Message types (classes) with identical names in streams the UNION applies to, must be binary identical (have identical number of fields, names, types, and order).
  • Union streams must be enclosed in brackets: select * from (stream1 union stream2).
# Aggregation of bars from three streams

with
this.entries[this is L1Entry][0] as trade
select
trade.exchangeId as exhcnage,
max{}(trade.price) as high,
min{}(trade.price) as low,
first{}(trade.price) as open,
last{}(trade.price) as close
from (binance UNION kraken UNION bitfinex)
over time(5s)
where trade != null and symbol == 'BTC/USD'